﻿//======================================================
//==     (c)2008 aspxcms inc by NeTCMS v1.0              ==
//==          Forum:bbs.aspxcms.com                   ==
//==         Website:www.aspxcms.com                  ==
//======================================================
using System;
using System.Data;
using System.Data.SqlClient;
using NetCMS.DALFactory;
using NetCMS.Model;
using NetCMS.Common;
using System.Text.RegularExpressions;
using System.Text;
using System.Reflection;
using NetCMS.DALProfile;
using NetCMS.Config;

namespace NetCMS.DALSQLServer
{
    public class Ghistory : DbBase, IGhistory
    {

        public DataTable sel_sysUser(string UserNum,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@UserNum", UserNum);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select UserGroupNumber,iPoint,gPoint,SiteID,UserName From " + Pre + "sys_user where UserNum=@UserNum";
            }
            else if (flag == 1)
            {
                Sql = "select UserName,PassQuestion,PassKey,Email,UserNum from " + Pre + "sys_User where UserName=@UserNum";
            }
            else if (flag == 2)
            {
                Sql = "Select CardNumber,CardPassWord,Money,TimeOutDate,isUse,isLock,isBuy,Point From " + Pre + "user_Card  where CardNumber=@UserNum";
            }
            else if (flag == 3)
            {
                Sql = "select TopTitle,GoodTitle,CheckTtile,OCTF,DelSelfTitle,DelOTitle,EditSelfTitle,EditOtitle,ReadTitle,GIChange,GTChageRate from " + Pre + "user_Group where GroupNumber=@UserNum";
            }
            else if (flag == 4)//mycom_Look.aspx 
            {
                Sql = "select Title,Content from " + Pre + "api_commentary where Commid=@UserNum";
            }
            else if (flag == 5)
            {
                Sql = "Select ClassCName From " + Pre + "friend_class where ClassID=@UserNum";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, param);
            #endregion
        }
        
        public int del_userInfo(string ID,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@GhID", ID);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "delete " + Pre + "user_Ghistory  where  GhID=@GhID";
            }
            else if (flag == 1)//collection.aspx
            {
                Sql = "delete " + Pre + "API_Faviate where FID=@GhID And UserNum='" + NetCMS.Global.Current.UserNum + "'";
            }
            else if (flag == 2)
            {
                Sql = "delete " + Pre + "api_commentary  where Commid=@GhID";
            }
            else if (flag == 3)
            {
                Sql = "delete " + Pre + "user_Ghistory  where  GhID=@GhID " + NetCMS.Common.Public.getSessionStr() + "";
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }

        public int sel_sysUserInfo(string UserNum, string UserPassword)
        {
            #region
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@UserNum", UserNum), new SqlParameter("@UserPassword", UserPassword) };
            int flg = 0;
            string Sql = "select ID from " + Pre + "sys_User where UserNum=@UserNum and UserPassword=@UserPassword";
            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, Sql, param);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    flg = 1;
                }
                dt.Clear(); dt.Dispose();
            }
            return flg;
            #endregion
        }

        public int update_sysUser(int Money1, string UserNum,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@UserNum", UserNum);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "update " + Pre + "sys_User set gPoint=gPoint+" + Money1 + " where UserNum=@UserNum";
            }
            else if (flag == 1)
            {
                Sql = "update " + Pre + "sys_User set iPoint=iPoint+" + Money1 + " where UserNum=@UserNum";
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }

        public int update_userInfos(string UserNum, string cnm,int flag)
        {
            #region
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserNum", SqlDbType.NVarChar, 15);
            param[0].Value = UserNum;
            param[1] = new SqlParameter("@CardNumber", SqlDbType.NVarChar, 32);
            param[1].Value = cnm;

            string Sql = null;
            if (flag == 0)
            {
                Sql = "update " + Pre + "user_Card set isUse='1',UserNum=@UserNum where CardNumber=@CardNumber";
            }
            else if (flag == 1)//getPassword.aspx
            {
                //md5码是16位的，用参数传的话就是15位了
                Sql = "update " + Pre + "sys_User set UserPassword='" + UserNum + "' where UserNum=@CardNumber";
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }
        public bool addTo(string NewsID,int ChID)
        {
            #region
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@NewsID", SqlDbType.NVarChar, 15);
            param[0].Value = NewsID;
            param[1] = new SqlParameter("@ChID", SqlDbType.Int, 4);
            param[1].Value = ChID;
            string gsql = "select count(id) from " + Pre + "API_Faviate where FID=@NewsID and ChID=@ChID And UserNum='" + NetCMS.Global.Current.UserNum + "'";
            int i_Count = Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, gsql, param));
            if (i_Count == 0)
            {
                string Sql = "insert " + Pre + "API_Faviate(FID,UserNum,CreatTime,APIID,DataLib,ChID) values(@NewsID,'" + NetCMS.Global.Current.UserNum + "','" + DateTime.Now + "','0','',@ChID)";
                DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
                return true;
            }
            return false;
            #endregion
        }

        #region Exchange.aspx
        public int Add(GhistoryInfo Gh, int ghtype, string UserNum, string content)
        {
            #region
            string Sql = "insert into " + Pre + "User_Ghistory(GhID,ghtype,Gpoint,iPoint,Money,CreatTime,UserNUM,gtype,content) values(@GhID,'" + ghtype + "',@Gpoint,@iPoint,0,@CreatTime,@UserNum,3,@content)";
            SqlParameter[] parm = GetGhistory(Gh);
            int i_length = parm.Length;
            Array.Resize<SqlParameter>(ref parm, i_length + 2);
            parm[i_length] = new SqlParameter("@UserNum", UserNum);
            parm[i_length + 1] = new SqlParameter("@content", content);
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, parm);
            #endregion
        }
        private SqlParameter[] GetGhistory(GhistoryInfo Gh)
        {
            #region
            SqlParameter[] parm = new SqlParameter[4];
            parm[0] = new SqlParameter("@GhID", SqlDbType.NVarChar, 50);
            parm[0].Value = Rand.Number(12);
            parm[1] = new SqlParameter("@Gpoint", SqlDbType.NVarChar, 50);
            parm[1].Value = Gh.Gpoint;
            parm[2] = new SqlParameter("@iPoint", SqlDbType.NVarChar, 50);
            parm[2].Value = Gh.iPoint;
            parm[3] = new SqlParameter("@CreatTime", SqlDbType.DateTime);
            parm[3].Value = DateTime.Now;
            return parm;
            #endregion
        }
        #endregion
       
        public int sel_userCardInfo(string cnm)
        {
            #region
            SqlParameter param = new SqlParameter("@CardNumber", cnm);
            int flg = 0;
            string Sql = "Select isUse From " + Pre + "user_Card where CardNumber=@CardNumber";
            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, Sql, param);
            if (dt != null && dt.Rows.Count > 0)
            {
                flg = int.Parse(dt.Rows[0]["isUse"].ToString());
                dt.Clear(); dt.Dispose();
            }
            return flg;
            #endregion
        }

        public int sel_sysPramUser()
        {
            #region
            int flg = 0;
            string Sql = "select GhClass from " + Pre + "sys_PramUser";
            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                flg = int.Parse(dt.Rows[0]["GhClass"].ToString());
                dt.Clear(); dt.Dispose();
            }
            return flg;
            #endregion
        }

        public int add_userGhistory(string GhID, string UserNum, int Gpoint, int Money, DateTime CreatTime, string content)
        {
            #region
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@GhID", SqlDbType.NVarChar, 12);
            param[0].Value = GhID;
            param[1] = new SqlParameter("@UserNum", SqlDbType.NVarChar, 15);
            param[1].Value = UserNum;
            param[2] = new SqlParameter("@content", SqlDbType.NText);
            param[2].Value = content;

            string Sql = "insert into " + Pre + "User_Ghistory(GhID,UserNUM,Gpoint,ghtype,Money,CreatTime,gtype,content) values(@GhID,@UserNum," + Gpoint + ",1," + Money + ",'" + CreatTime + "',2,@content)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }

        #region mycom.aspx
        public DataTable GetPage(string title, string Um, string dtm1, string dtm2, string isCheck, string islock, string SiteID, string UserNum, int DelOTitle, int EditOtitle, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            #region
            string QSQL = "";
            if (title != "" && title != null)
            {
                QSQL = " and Title like '%" + title + "%'";
            }
            if (dtm1 != "" && dtm1 != null && dtm2 != "" && dtm2 != null)
            {
                DateTime dtms1 = DateTime.Parse(dtm1);
                DateTime dtms2 = DateTime.Parse(dtm2);
                QSQL += " and creatTime >= '" + dtms1 + "' and  creatTime <= '" + dtms2 + "'";
            }
            if (isCheck != "" && isCheck != null && isCheck != "0")
            {
                QSQL += " and isCheck=@isCheck";
            }
            if (islock != "" && islock != null && islock != "0")
            {
                int islocks = 0;
                if (islock == "1")
                {
                    islocks = 0;
                }
                else
                {
                    islocks = 1;
                }
                QSQL += " and islock = '" + islocks + "'";
            }
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@UserNum", UserNum), new SqlParameter("@SiteID", SiteID) };
            string sl = null;
            if (UserNum != "")
            {
                sl = "" + Pre + "api_commentary  where  UserNum=@UserNum and SiteID=@SiteID " + QSQL + "";
            }
            else
            {
                sl = "" + Pre + "api_commentary where SiteID=@SiteID " + QSQL + "";
            }
            string AllFields = "Commid,Title,InfoID,APIID,creatTime,isCheck,UserNum,islock,OrderID,GoodTitle,Content,Datalib";
            string Condition = sl;
            string IndexField = "Id";
            string OrderFields = "order by OrderID Desc";
            return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            #endregion
        }
        #endregion

        #region mycom_up.aspx
        public int update_apiCommentary(string Title, string Contents, DateTime CreatTime, string Commid, int islock)
        {
            #region
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@Title", SqlDbType.NVarChar, 200);
            param[0].Value = "";
            param[1] = new SqlParameter("@Contents", SqlDbType.NVarChar, 200);
            param[1].Value = Contents;
            param[2] = new SqlParameter("@Commid", SqlDbType.NVarChar, 12);
            param[2].Value = Commid;
            param[3] = new SqlParameter("@CreatTime", SqlDbType.DateTime, 8);
            param[3].Value = CreatTime;
            string Tmp = "";
            if (islock != 2)
            {
                Tmp = ",islock=" + islock + "";
            }
            string Sql = "update " + Pre + "api_commentary set Title=@Title,Content=@Contents,creatTime=@creatTime " + Tmp + " where Commid=@Commid";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }
        #endregion

        #region pointhistory.aspx
        public DataTable GetPagepoi(string typep, string UM, string sle_NUM, string SiteID, string UserNum, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            #region
            string gtype = string.Empty;
            string gtypes = string.Empty;
            if (typep != "0" && typep != null)
            {
                gtype = "and gtype=@gtype";
            }
            if (typep == "8")
            {
                gtypes = "and ghtype=1";
            }
            if (typep == "9")
            {
                gtypes = "and ghtype=0";
            }
            string UserNumstr = "";
            if (UserNum != "" && UserNum != null)
            {
                UserNumstr = " and UserNum=@UserNum";
            }
            string sel_UM = string.Empty;
            if (UM != string.Empty)
            {
                if (sle_NUM != string.Empty)
                {
                    sel_UM = " and UserNUM=@sle_NUM";
                }
                if (typep == "8" || typep == "9")
                {
                    gtype = null;
                }
                else
                {
                    gtypes = null;
                }
            }
            string siteID1 = "";
            if (SiteID != "" && SiteID != "0" && SiteID != null)
            {
                if (NetCMS.Global.Current.SiteID == "0")
                {
                    siteID1 = " and SiteID='" + SiteID + "'";
                }
                else
                {
                    siteID1 = " and SiteID='" + NetCMS.Global.Current.SiteID + "'";
                }
            }
            else
            {
                siteID1 = " and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@gtype", int.Parse(typep)), new SqlParameter("@UserNum", UserNum), new SqlParameter("@sle_NUM", sle_NUM) };
            string AllFields = "GhID,ghtype,Gpoint,iPoint,Money,CreatTime,UserNUM,gtype,content";
            string Condition = "" + Pre + "user_Ghistory where 1=1 " + gtype + sel_UM + siteID1 + gtypes + UserNumstr + "";
            string IndexField = "id";
            string OrderFields = "order by id";
            return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            #endregion
        }
        #endregion

        public int update_userInfo(int ipoint2, int gpoint2, string UserNum)
        {
            SqlParameter param = new SqlParameter("@UserNum", UserNum);
            string Sql = "update " + Pre + "sys_user set iPoint='" + ipoint2 + "',gPoint='" + gpoint2 + "' where UserNum=@UserNum";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
        }
        #region 前台友情连接
        public DataTable sel_friendInfo(int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select IsOpen,Content From " + Pre + "friend_pram";
            }
            else if (flag == 1)
            {
                Sql = "Select ClassID,ClassCName,ParentID From " + Pre + "friend_class";
            }
            else if (flag == 2)
            {
                Sql = "Select UserNum,Email From " + Pre + "sys_User where UserNum='" + NetCMS.Global.Current.UserNum + "'";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            #endregion
        }
        public int ISExitNamee(string Str_Name)
        {
            SqlParameter param = new SqlParameter("@Name", Str_Name);
            string Str_CheckSql = "Select count(Name) From " + Pre + "friend_link Where Name=@Name";
            return (int)DbHelper.ExecuteScalar(CommandType.Text, Str_CheckSql, param);
        }
        public int SaveLink(string Str_Class, string Str_Name, string Str_Type, string Str_Url, string Str_Content, string Str_PicUrl, string Str_Author, string Str_Mail, string Str_ContentFor)
        {
            #region
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@Str_Class", SqlDbType.NVarChar, 12);
            param[0].Value = Str_Class;
            param[1] = new SqlParameter("@Str_Name", SqlDbType.NVarChar,50);
            param[1].Value = Str_Name;
            param[2] = new SqlParameter("@Str_Type", SqlDbType.Int, 1);
            param[2].Value = Convert.ToInt32(Str_Type);
            param[3] = new SqlParameter("@Str_Url", SqlDbType.NVarChar, 250);
            param[3].Value = Str_Url;
            param[4] = new SqlParameter("@Str_Content", SqlDbType.NText);
            param[4].Value = Str_Content;
            param[5] = new SqlParameter("@Str_PicUrl", SqlDbType.NVarChar, 250);
            param[5].Value = Str_PicUrl;
            param[6] = new SqlParameter("@Str_Author", SqlDbType.NVarChar, 50);
            param[6].Value = Str_Author;
            param[7] = new SqlParameter("@Str_Mail", SqlDbType.NVarChar, 150);
            param[7].Value = Str_Mail;
            param[8] = new SqlParameter("@Str_ContentFor", SqlDbType.NText);
            param[8].Value = Str_ContentFor;

            string Str_InSql = "Insert into " + Pre + "friend_link (ClassID,Name,Type,Url,Content,PicUrl,Author,Mail,ContentFor,isAdmin,Lock,IsUser,SiteID) Values(@Str_Class,@Str_Name,@Str_Type,@Str_Url,@Str_Content,@Str_PicUrl,@Str_Author,@Str_Mail,@Str_ContentFor,0,1,1,'" + NetCMS.Global.Current.SiteID + "')";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Str_InSql, param);
            #endregion
        }

        public void del_friendLink(string id)
        {
            SqlParameter param = new SqlParameter("@ID", id);
            string Str_InSql = "delete from " + Pre + "friend_link where Author='" + NetCMS.Global.Current.UserNum + "' and ID=@ID";
            DbHelper.ExecuteNonQuery(CommandType.Text, Str_InSql, param);
        }

        public DataTable sel_friendLink(int num, string uid)
        {
            SqlParameter param = new SqlParameter("@Author", uid);
            string Str_InSql = "select Name,Url,Content,PicUrl,Author,LinkContent from " + Pre + "friend_link where Author=@Author and Type=" + num + "";
            return DbHelper.ExecuteTable(CommandType.Text, Str_InSql, param);
        }
        #endregion
    }
}
